
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 11/01/2013 14:28:20
-- Generated from EDMX file: E:\My Data\Work\eStore\e.Store\e.Store.Web\e.Store.DataAccess\eStore_DBContext.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [eStote_DB];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_Product_Category]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Product] DROP CONSTRAINT [FK_Product_Category];
GO
IF OBJECT_ID(N'[dbo].[FK_Product_Supplier]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Product] DROP CONSTRAINT [FK_Product_Supplier];
GO
IF OBJECT_ID(N'[dbo].[FK_Product_User]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Product] DROP CONSTRAINT [FK_Product_User];
GO
IF OBJECT_ID(N'[dbo].[FK_RefMenuRole_Menu]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[RefMenuRole] DROP CONSTRAINT [FK_RefMenuRole_Menu];
GO
IF OBJECT_ID(N'[dbo].[FK_RefMenuRole_Role]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[RefMenuRole] DROP CONSTRAINT [FK_RefMenuRole_Role];
GO
IF OBJECT_ID(N'[dbo].[FK_User_Role]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[User] DROP CONSTRAINT [FK_User_Role];
GO
IF OBJECT_ID(N'[dbo].[FK_User_User]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[User] DROP CONSTRAINT [FK_User_User];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Category]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Category];
GO
IF OBJECT_ID(N'[dbo].[Menu]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Menu];
GO
IF OBJECT_ID(N'[dbo].[Product]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Product];
GO
IF OBJECT_ID(N'[dbo].[RefMenuRole]', 'U') IS NOT NULL
    DROP TABLE [dbo].[RefMenuRole];
GO
IF OBJECT_ID(N'[dbo].[Role]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Role];
GO
IF OBJECT_ID(N'[dbo].[Supplier]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Supplier];
GO
IF OBJECT_ID(N'[dbo].[User]', 'U') IS NOT NULL
    DROP TABLE [dbo].[User];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Categories'
CREATE TABLE [dbo].[Categories] (
    [CategoryOID] bigint IDENTITY(1,1) NOT NULL,
    [CategoryName] varchar(200)  NOT NULL,
    [CategoryDescription] varchar(500)  NULL
);
GO

-- Creating table 'Menus'
CREATE TABLE [dbo].[Menus] (
    [MenuOID] bigint IDENTITY(1,1) NOT NULL,
    [MenuName] varchar(100)  NOT NULL,
    [ControllerName] varchar(100)  NOT NULL,
    [DefaultActionName] varchar(50)  NOT NULL
);
GO

-- Creating table 'Products'
CREATE TABLE [dbo].[Products] (
    [ProductOID] bigint IDENTITY(1,1) NOT NULL,
    [ProductCode] varchar(100)  NOT NULL,
    [ProductName] nchar(10)  NOT NULL,
    [Description] varchar(100)  NULL,
    [SupplierOID] bigint  NOT NULL,
    [CategoryOID] bigint  NOT NULL,
    [BasePrice] bigint  NOT NULL,
    [SellingPrice] bigint  NOT NULL,
    [Quantity] bigint  NOT NULL,
    [Discount] bigint  NOT NULL,
    [SalesTax] decimal(18,2)  NOT NULL,
    [Active] bit  NOT NULL,
    [Comments] varchar(200)  NULL,
    [CreatedBy] bigint  NOT NULL,
    [CreatedDate] datetime  NOT NULL,
    [Image] varchar(max)  NULL
);
GO

-- Creating table 'RefMenuRoles'
CREATE TABLE [dbo].[RefMenuRoles] (
    [RefMenuRole1] bigint IDENTITY(1,1) NOT NULL,
    [RoleOID] bigint  NOT NULL,
    [MenuOID] bigint  NOT NULL
);
GO

-- Creating table 'Roles'
CREATE TABLE [dbo].[Roles] (
    [RoleOID] bigint IDENTITY(1,1) NOT NULL,
    [RoleName] varchar(200)  NOT NULL
);
GO

-- Creating table 'Suppliers'
CREATE TABLE [dbo].[Suppliers] (
    [SupplierOID] bigint IDENTITY(1,1) NOT NULL,
    [SupplierName] varchar(200)  NOT NULL,
    [ContactName] varchar(100)  NOT NULL,
    [PhoneNum] bigint  NULL,
    [MobileNum] bigint  NULL,
    [Address] varchar(100)  NULL
);
GO

-- Creating table 'Users'
CREATE TABLE [dbo].[Users] (
    [UserOID] bigint IDENTITY(1,1) NOT NULL,
    [Username] varchar(50)  NOT NULL,
    [Password] varchar(1000)  NOT NULL,
    [RoleOID] bigint  NOT NULL,
    [CreatedBy] bigint  NOT NULL,
    [MobileNum] bigint  NULL,
    [PhoneNum] bigint  NULL,
    [EmailID] varchar(200)  NULL,
    [Address] varchar(1000)  NULL,
    [CreatedDate] datetime  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [CategoryOID] in table 'Categories'
ALTER TABLE [dbo].[Categories]
ADD CONSTRAINT [PK_Categories]
    PRIMARY KEY CLUSTERED ([CategoryOID] ASC);
GO

-- Creating primary key on [MenuOID] in table 'Menus'
ALTER TABLE [dbo].[Menus]
ADD CONSTRAINT [PK_Menus]
    PRIMARY KEY CLUSTERED ([MenuOID] ASC);
GO

-- Creating primary key on [ProductOID] in table 'Products'
ALTER TABLE [dbo].[Products]
ADD CONSTRAINT [PK_Products]
    PRIMARY KEY CLUSTERED ([ProductOID] ASC);
GO

-- Creating primary key on [RefMenuRole1] in table 'RefMenuRoles'
ALTER TABLE [dbo].[RefMenuRoles]
ADD CONSTRAINT [PK_RefMenuRoles]
    PRIMARY KEY CLUSTERED ([RefMenuRole1] ASC);
GO

-- Creating primary key on [RoleOID] in table 'Roles'
ALTER TABLE [dbo].[Roles]
ADD CONSTRAINT [PK_Roles]
    PRIMARY KEY CLUSTERED ([RoleOID] ASC);
GO

-- Creating primary key on [SupplierOID] in table 'Suppliers'
ALTER TABLE [dbo].[Suppliers]
ADD CONSTRAINT [PK_Suppliers]
    PRIMARY KEY CLUSTERED ([SupplierOID] ASC);
GO

-- Creating primary key on [UserOID] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [PK_Users]
    PRIMARY KEY CLUSTERED ([UserOID] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [CategoryOID] in table 'Products'
ALTER TABLE [dbo].[Products]
ADD CONSTRAINT [FK_Product_Category]
    FOREIGN KEY ([CategoryOID])
    REFERENCES [dbo].[Categories]
        ([CategoryOID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Product_Category'
CREATE INDEX [IX_FK_Product_Category]
ON [dbo].[Products]
    ([CategoryOID]);
GO

-- Creating foreign key on [MenuOID] in table 'RefMenuRoles'
ALTER TABLE [dbo].[RefMenuRoles]
ADD CONSTRAINT [FK_RefMenuRole_Menu]
    FOREIGN KEY ([MenuOID])
    REFERENCES [dbo].[Menus]
        ([MenuOID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RefMenuRole_Menu'
CREATE INDEX [IX_FK_RefMenuRole_Menu]
ON [dbo].[RefMenuRoles]
    ([MenuOID]);
GO

-- Creating foreign key on [SupplierOID] in table 'Products'
ALTER TABLE [dbo].[Products]
ADD CONSTRAINT [FK_Product_Supplier]
    FOREIGN KEY ([SupplierOID])
    REFERENCES [dbo].[Suppliers]
        ([SupplierOID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Product_Supplier'
CREATE INDEX [IX_FK_Product_Supplier]
ON [dbo].[Products]
    ([SupplierOID]);
GO

-- Creating foreign key on [CreatedBy] in table 'Products'
ALTER TABLE [dbo].[Products]
ADD CONSTRAINT [FK_Product_User]
    FOREIGN KEY ([CreatedBy])
    REFERENCES [dbo].[Users]
        ([UserOID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Product_User'
CREATE INDEX [IX_FK_Product_User]
ON [dbo].[Products]
    ([CreatedBy]);
GO

-- Creating foreign key on [RoleOID] in table 'RefMenuRoles'
ALTER TABLE [dbo].[RefMenuRoles]
ADD CONSTRAINT [FK_RefMenuRole_Role]
    FOREIGN KEY ([RoleOID])
    REFERENCES [dbo].[Roles]
        ([RoleOID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RefMenuRole_Role'
CREATE INDEX [IX_FK_RefMenuRole_Role]
ON [dbo].[RefMenuRoles]
    ([RoleOID]);
GO

-- Creating foreign key on [RoleOID] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [FK_User_Role]
    FOREIGN KEY ([RoleOID])
    REFERENCES [dbo].[Roles]
        ([RoleOID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_User_Role'
CREATE INDEX [IX_FK_User_Role]
ON [dbo].[Users]
    ([RoleOID]);
GO

-- Creating foreign key on [CreatedBy] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [FK_User_User]
    FOREIGN KEY ([CreatedBy])
    REFERENCES [dbo].[Users]
        ([UserOID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_User_User'
CREATE INDEX [IX_FK_User_User]
ON [dbo].[Users]
    ([CreatedBy]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------